﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDACompany
    {

        public static List<Company> Select(Company company)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _url = ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();
            if (company.CompanyAddress.Count > 0)
                _url = company.CompanyAddress[0].UrldOmainAddress;

            string _cmdText =
                 "select distinct c.* " +
                 "FROM Company c " +
                 "LEFT JOIN companyaddress ca ON c.id = ca.companyid " +
                 "WHERE ((c.ID = " + company.ID.ToString() + ") OR (" + company.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + "))" +
                 "  AND ((ca.URLDomainAddress = '" + _url + "') OR ('" + _url + "' = '" + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + "'))" +
                 "ORDER BY c.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<Company> _companyList = new List<Company>();

            foreach (DataRow row in tbl.Rows)
            {
                Company _company = new Company();
                _company.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _company.Name = (row["Name"].ToString() == "" ? null : Convert.ToString(row["Name"]));
                _company.Telephone = (row["Telephone"].ToString() == "" ? null : Convert.ToString(row["Telephone"]));
                _company.Address = (row["Address"].ToString() == "" ? null : Convert.ToString(row["Address"]));
                _company.Fax = (row["Fax"].ToString() == "" ? null : Convert.ToString(row["Fax"]));
                _company.Email = (row["Email"].ToString() == "" ? null : Convert.ToString(row["Email"]));
                _company.FbuRl = (row["FBUrl"].ToString() == "" ? null : Convert.ToString(row["FBUrl"]));
                _company.IsShowFb = (row["IsShowFb"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowFb"]));
                _company.IsShowGallery = (row["IsShowGallery"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowGallery"]));
                _company.IsShowWeeklyMenu = (row["IsShowWeeklyMenu"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowWeeklyMenu"]));
                _company.ThemeFolder = (row["ThemeFolder"].ToString() == "" ? null : Convert.ToString(row["ThemeFolder"]));

                _companyList.Add(_company);
            }

            con.Close();
            return _companyList;
        }

        public static List<Company> SelectByName(Company company)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            //string _url = ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();
            //if (company.CompanyAddress.Count > 0)
            //    _url = company.CompanyAddress[0].UrldOmainAddress;

            string _cmdText =
                 "select distinct c.* " +
                 "FROM Company c " +
                 "LEFT JOIN companyaddress ca ON c.id = ca.companyid " +
                 "WHERE ((c.ID = " + company.ID.ToString() + ") OR (" + company.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + "))" +
                 "  AND ((c.Name = '" + company.Name + "') OR ('" + company.Name + "' = ''))" +
                 //"  AND ((ca.URLDomainAddress = '" + _url + "') OR ('" + _url + "' = '" + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + "'))" +
                 "ORDER BY c.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<Company> _companyList = new List<Company>();

            foreach (DataRow row in tbl.Rows)
            {
                Company _company = new Company();
                _company.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _company.Name = (row["Name"].ToString() == "" ? null : Convert.ToString(row["Name"]));
                _company.Telephone = (row["Telephone"].ToString() == "" ? null : Convert.ToString(row["Telephone"]));
                _company.Address = (row["Address"].ToString() == "" ? null : Convert.ToString(row["Address"]));
                _company.Fax = (row["Fax"].ToString() == "" ? null : Convert.ToString(row["Fax"]));
                _company.Email = (row["Email"].ToString() == "" ? null : Convert.ToString(row["Email"]));
                _company.FbuRl = (row["FBUrl"].ToString() == "" ? null : Convert.ToString(row["FBUrl"]));
                _company.IsShowFb = (row["IsShowFb"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowFb"]));
                _company.IsShowGallery = (row["IsShowGallery"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowGallery"]));
                _company.IsShowWeeklyMenu = (row["IsShowWeeklyMenu"].ToString() == "" ? false : Convert.ToBoolean(row["IsShowWeeklyMenu"]));

                _companyList.Add(_company);
            }

            con.Close();
            return _companyList;
        }
    }
}